/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import model.Diaria;

/**
 * Document   : DiariaDAO
 * 
 * Created on : 01/03/2013, 10:48:33 
 * 
 * Author     : Fiúza <rbfiuza@gmail.com>
 */ 

public class DiariaDAO {
    
    Connection conn = null;
    PreparedStatement stmt = null;
    String sql = null;

    public ArrayList<Diaria> getDiaria() {
        ArrayList<Diaria> diariaList = new ArrayList<Diaria>();

        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "mysql");

            sql = "Select"
                    + " id"
                    + ",diaria"
                    + ",encontros"
                    + ",semestres"
                    + ",pessoas"
                    + ",encontros_polo"
                    + ",diarias_encontro"
                    + ",polos"
                    + ",diarias_aprovadas"
                    + ",indicacoes_parametros"
                    + ",valor_unitario"
                    + ",valor_final"
                + " from diaria where 1  = ?";

            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, 1);

            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                Diaria diaria = new Diaria();
                diaria.setId(rs.getInt("id"));
                diaria.setDiaria(rs.getString("diaria"));
                diaria.setEncontros(rs.getInt("encontros"));
                diaria.setSemestres(rs.getInt("semestres"));
                diaria.setPessoas(rs.getInt("pessoas"));
                diaria.setEncontros_polo(rs.getInt("encontros_polo"));
                diaria.setDiarias_encontro(rs.getInt("diarias_encontro"));
                diaria.setPolos(rs.getInt("polos"));
                diaria.setDiarias_aprovadas(rs.getInt("diarias_aprovadas"));
                diaria.setValor_unitario(rs.getFloat("valor_unitario"));
                diaria.setValor_final(rs.getFloat("valor_final"));
                diaria.setIndicacoes_parametros(rs.getString("indicacoes_parametros"));
                diariaList.add(diaria);
            }

            rs.close();
            stmt.close();
            stmt = null;


            conn.close();
            conn = null;

        } catch (Exception e) {
            System.out.println(e);
        } finally {

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException sqlex) {
                    // ignore -- as we can't do anything about it here           
                }

                stmt = null;
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException sqlex) {
                    // ignore -- as we can't do anything about it here     
                }

                conn = null;
            }
        }

        return diariaList;
    }

    public String insertDiaria() {
        String userId = null;

        return userId;
    }

    public boolean updateDiaria(String storeId, Diaria diaria) {
        boolean success = true;

        try {

            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "mysql");

            sql = "UPDATE diaria set   "
                    + " id = ?"
                    + " ,diaria = ?"
                    + " ,encontros = ?"
                    + " ,semestres = ?"
                    + " ,pessoas = ?"
                    + " ,encontros_polo = ?"
                    + " ,diarias_encontro = ?"
                    + " ,polos = ?"
                    + " ,diarias_aprovadas = ?"
                    + " ,valor_unitario = ?"
                    + " ,valor_final = ?"
                    + " ,indicacoes_parametros = ?"
                    + " where id = ?";
            
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, diaria.getId());
            stmt.setString(2, diaria.getDiaria());
            stmt.setInt(3, diaria.getEncontros());
            stmt.setInt(4, diaria.getSemestres());
            stmt.setInt(5, diaria.getPessoas());
            stmt.setInt(6, diaria.getEncontros_polo());
            stmt.setInt(7, diaria.getDiarias_encontro());
            stmt.setInt(8, diaria.getPolos());
            stmt.setInt(9, diaria.getDiarias_aprovadas());
            stmt.setFloat(10, diaria.getValor_unitario());
            stmt.setFloat(11, diaria.getValor_final());
            stmt.setString(12, diaria.getIndicacoes_parametros());
            stmt.setInt(13, diaria.getId());

            stmt.executeUpdate();

            stmt.close();
            stmt = null;
            conn.close();
            conn = null;

        } catch (Exception e) {

            success = false;
            System.out.println(e);

        } finally {

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException sqlex) {
                    // ignore -- as we can't do anything about it here           
                }

                stmt = null;
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException sqlex) {
                    // ignore -- as we can't do anything about it here     
                }

                conn = null;
            }
        }

        return success;
    }
}